package com.thinktank.dao;

import static com.thinktank.util.DbUtil.*;

import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.*;

import com.thinktank.vo.Board;
import com.thinktank.vo.Reply;
import com.thinktank.dao.BoardDao;

public class BoardDao {
	private Connection con = null;
	public BoardDao(Connection con) {
		super();
		this.con = con;
	}
	public static final int NPG = 5;

	public int getCount(String field, String query, String icategory){
		int count = 0;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		try {
			if(icategory.equals("0")){
				
			String sql = "SELECT COUNT(*) FROM BOARD WHERE "+field+" LIKE ?";
			ps = con.prepareStatement(sql);
			ps.setString(1, "%"+query+"%");
			
			} else{
				String sql = "SELECT COUNT(*) FROM BOARD WHERE (BD_CATEGORY=?) AND ("+field+" LIKE ?)";
				ps = con.prepareStatement(sql);
				ps.setString(1, icategory);
				ps.setString(2, "%"+query+"%");
			}			
			
			rs = ps.executeQuery();
			
			int temp = 0;
			while(rs.next()){
				temp = rs.getInt(1);
			}
			count = (int)Math.ceil(temp/5.0);
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close(ps);
		}
		return count;
	}
	
	
	public List<Board> getAllBoards(int page, String field, String query, String icategory, String url) {
		int start = 1 + NPG * (page-1);
		int end = 5 + NPG * (page-1);
		
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<Board> list = null;		

		try {			
			if(icategory.equals("0")){
				String sql = "SELECT * FROM "
						+ "(SELECT ROWNUM NUM, TEMP.* FROM "
						+ "(SELECT * FROM BOARD WHERE ("+ field +" LIKE ?)AND (BD_WRITER = (SELECT MB_NO FROM MEMBER WHERE MB_PAGE=?)) ORDER BY BD_DATE DESC ) TEMP) "
						+ "WHERE NUM BETWEEN ? AND ?";				
				pstmt = con.prepareStatement(sql);			
				pstmt.setString(1, "%"+query+"%");
				pstmt.setString(2, url);
				pstmt.setInt(3, start);
				pstmt.setInt(4, end);		
			
			} else{
				String sql = "SELECT * FROM "
						+ "(SELECT ROWNUM NUM, TEMP.* FROM "
						+ "(SELECT * FROM BOARD WHERE (BD_CATEGORY=?) AND ("+ field +" LIKE ?) AND (BD_WRITER = (SELECT MB_NO FROM MEMBER WHERE MB_PAGE=?)) ORDER BY BD_DATE DESC ) TEMP) "
						+ "WHERE NUM BETWEEN ? AND ?";
			
				pstmt = con.prepareStatement(sql);
				pstmt.setString(1, icategory);
				pstmt.setString(2, "%"+query+"%");
				pstmt.setString(3, url);
				pstmt.setInt(4, start);
				pstmt.setInt(5, end);		
			}	
			rs = pstmt.executeQuery();
			list = new ArrayList<Board>();
	
			while(rs.next()) {
				Board board = new Board();
				board.setbNo(rs.getString("bd_no"));
				board.setbSubject(rs.getString("bd_subject"));
				board.setbDate(rs.getDate("bd_date"));
				board.setbDate2(rs.getTimestamp("bd_date"));
				board.setbContent(rs.getString("bd_content"));
				board.setbSub(rs.getString("bd_sub"));
				board.setbCategory(rs.getString("bd_category"));
				list.add(board);
			}
		}	catch (Exception e) {
				e.printStackTrace();
		} finally {
			close(pstmt);
			close(rs);			
		}	
		return list;
	}
	
	public List<Reply> getAllReplies(String bNo) {
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<Reply> list = null;		
		
		String sql = "SELECT * FROM REPLY WHERE BD_NO=?";	///
		
		try {
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, bNo);	
			rs = pstmt.executeQuery();
			list = new ArrayList<Reply>();
	
			while(rs.next()) {
				Reply reply = new Reply();
				
				reply.setrNo(rs.getString("r_no"));
				reply.setrWriter(rs.getString("r_writer"));
				reply.setrContent(rs.getString("r_content"));
				SimpleDateFormat sdf = new SimpleDateFormat ("yyyy.mm.dd a hh:mm"); 
				reply.setrDate(sdf.format(rs.getTimestamp("r_date")));
				reply.setbNo(rs.getString("bd_no"));
				
				list.add(reply);
			}
			close(pstmt);
			close(rs);	
		}	catch (Exception e) {
				e.printStackTrace();
		}	
		return list;
	}
	
	public int insert(Reply reply, String bNo){
		PreparedStatement psmt = null;
		int result = 0;
		try {
				String temp = "select Max(R_NO) from REPLY";
				psmt = con.prepareStatement(temp);
				ResultSet rs = psmt.executeQuery();
				rs.next();

				String rNo = rs.getInt(1) + 1 + "";
				for (int i = rNo.length(); i < 3; i++) 
					rNo = "0".concat(rNo);
				close(rs);
	
			
				String sql = "INSERT INTO REPLY VALUES ("
						+ "?, ?, ?, SYSDATE, ?)";
				psmt = con.prepareStatement(sql);
				psmt.setString(1, rNo);
				psmt.setString(2, reply.getrWriter());
				psmt.setString(3, reply.getrContent());
				psmt.setString(4, bNo);

				result = psmt.executeUpdate();
				close(psmt);
			} catch (Exception e) {
				e.printStackTrace();
			} 
			if(result != 0)		commit(con);
			return result;
	}
	
	public int replydelete(String rNo){
		PreparedStatement pstmt = null;
		int result =0;
		try {
			String sql = "DELETE FROM REPLY WHERE R_NO=?";
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, rNo);			
			result = pstmt.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close(pstmt);
		}
		if(result != 0)		commit(con);
		return result;
	}	
	
	public Board getBoard(String bNo){
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Board board = new Board();
		try {
			String sql = "SELECT * FROM BOARD WHERE BD_NO=?";
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, bNo);			
			rs = pstmt.executeQuery();			
			while(rs.next()){
				board.setbNo(rs.getString("bd_no"));
				board.setbSubject(rs.getString("bd_subject"));
				board.setbDate(rs.getDate("bd_date"));
				board.setbDate2(rs.getTimestamp("bd_date"));
				board.setbContent(rs.getString("bd_content"));
				board.setbSub(rs.getString("bd_sub"));
				board.setbCategory(rs.getString("bd_category"));
			}
			
			
		}	catch (Exception e) {
			e.printStackTrace();
		} finally {
			close(pstmt);
			close(rs);
		}
		return board;
	}	

	public int update(Board board){
		PreparedStatement pstmt = null;
		int result =0;
		try {
			String sql = "UPDATE BOARD SET BD_SUBJECT=?, BD_DATE=SYSDATE, BD_CONTENT=?, BD_CATEGORY=? WHERE BD_NO=?";
			System.out.println(sql);			
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, board.getbSubject());
			pstmt.setString(2, board.getbContent());
			pstmt.setString(3, board.getbCategory());
			pstmt.setString(4, board.getbNo());
			result = pstmt.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close(pstmt);
		}
		return result;
	}	
		
	public int insert(Board board){
		PreparedStatement psmt = null;
		int result = 0;
		String bNo = null;
		try {
				// 1. DB 내에서 MAX Gno를 받고 +1을 해서 gno 변수에 세팅
				String temp = "select Max(BD_NO) from BOARD";
				psmt = con.prepareStatement(temp);
				ResultSet rs = psmt.executeQuery();
				rs.next();

				bNo = rs.getInt(1) + 1 + "";
				for (int i = bNo.length(); i < 3; i++) 
					bNo = "0".concat(bNo);
				close(rs);
	
			
				String sql = "INSERT INTO BOARD VALUES ("
						+ "?, ?, SYSDATE, ?, 0, ?, ?)";
				psmt = con.prepareStatement(sql);
				psmt.setInt(1, Integer.parseInt(bNo));
				psmt.setString(2, board.getbSubject());
				//SYSDATE
				psmt.setString(3, board.getbContent());
				// SUB??
				psmt.setString(4, board.getbCategory());
				psmt.setString(5, board.getbWriter());
				
				result = psmt.executeUpdate();
				close(psmt);
			} catch (Exception e) {
				e.printStackTrace();
			} 
			if(result != 0){
				commit(con);
				result = Integer.parseInt(bNo);
			}
			return result;
		}	
	
	public int delete(String bNo){
		String query = "DELETE FROM ALBUM WHERE AB_NO=?";
		PreparedStatement ps = null;
		int result = 0;
		try{
			ps = con.prepareStatement(query);
			ps.setString(1, bNo);
			
			result = ps.executeUpdate();
		} catch(SQLException e){
			e.printStackTrace();
		} finally{
			close(ps);
		}
		
		PreparedStatement pstmt = null;
		try {
			String sql = "DELETE FROM BOARD WHERE BD_NO=?";
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, bNo);			
			result = pstmt.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close(pstmt);
		}
		if(result != 0)		commit(con);
		return result;
	}	
	
}